﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Business.Entity;
using System.Data.SqlClient;
using System.Data;

namespace Business.Dal
{
   public class IlanProvider:Provider<Ilan>
    {
        public override bool Insert(Ilan tObject)
        {
            bool returnValue = false;

            SqlCommand cmd = new SqlCommand("IlanEkle");
            cmd.Parameters.Add("@KategoriID", SqlDbType.VarChar).Value = tObject.KategoriID;
            //cmd.Parameters.Add("@KullaniciAdi", SqlDbType.VarChar).Value = tObject.KullaniciAdi;
            cmd.Parameters.Add("@UyeID", SqlDbType.Int).Value = tObject.UyeID;
            cmd.Parameters.Add("@UlkeID", SqlDbType.Int).Value = tObject.UlkeID;
            cmd.Parameters.Add("@SehirID", SqlDbType.Int).Value = tObject.SehirID;
            cmd.Parameters.Add("@IlceID", SqlDbType.Int).Value = tObject.IlceID;
            cmd.Parameters.Add("@MahalleID", SqlDbType.Int).Value = tObject.MahalleID;
            cmd.Parameters.Add("@IlanBasligi", SqlDbType.NVarChar).Value = tObject.IlanBasligi;
            cmd.Parameters.Add("@Aciklama", SqlDbType.NVarChar).Value = tObject.Aciklama;
            cmd.Parameters.Add("@Fiyat", SqlDbType.Decimal).Value = tObject.Fiyat;
            cmd.Parameters.Add("@ParaCinsiID", SqlDbType.Int).Value = tObject.ParaCinsiID;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Tools.Connection();
            Tools.Open();
            tObject.IlanID = Convert.ToInt32(cmd.ExecuteScalar());
            returnValue = true;
            return returnValue;
        }

        public override bool Update(Ilan tObject)
        {
            bool returnValue = false;

            SqlCommand cmd = new SqlCommand("IlanGuncelle");
            cmd.Parameters.Add("@IlanID", SqlDbType.Int).Value = tObject.IlanID;
            cmd.Parameters.Add("@KategoriID", SqlDbType.VarChar).Value = tObject.KategoriID;
            cmd.Parameters.Add("@KullaniciAdi", SqlDbType.VarChar).Value = tObject.KullaniciAdi;
            cmd.Parameters.Add("@UyeID", SqlDbType.Int).Value = tObject.UyeID;
            cmd.Parameters.Add("@UlkeID", SqlDbType.Int).Value = tObject.UyeID;
            cmd.Parameters.Add("@SehirID", SqlDbType.Int).Value = tObject.SehirID;
            cmd.Parameters.Add("@IlceID", SqlDbType.Int).Value = tObject.IlceID;
            cmd.Parameters.Add("@MahalleID", SqlDbType.Int).Value = tObject.MahalleID;
            cmd.Parameters.Add("@IlanBasligi", SqlDbType.NVarChar).Value = tObject.IlanBasligi;
            cmd.Parameters.Add("@Aciklama", SqlDbType.NVarChar).Value = tObject.Aciklama;
            cmd.Parameters.Add("@Fiyat", SqlDbType.Decimal).Value = tObject.Fiyat;
            cmd.Parameters.Add("@ParaCinsiID", SqlDbType.Int).Value = tObject.ParaCinsiID;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Tools.Connection();
            Tools.Open();
            
            returnValue = Convert.ToBoolean(cmd.ExecuteNonQuery());

            return returnValue;
            
       }

        public override bool Delete(int RecordID)
        {
            bool returnValue = false;

            SqlCommand cmd = new SqlCommand("IlanSil");
            cmd.Parameters.Add("@IlanID", SqlDbType.Int).Value = RecordID;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Tools.Connection();
            Tools.Open();
            
            returnValue = Convert.ToBoolean(cmd.ExecuteNonQuery());
            return returnValue;
        }

        public List<Ilan> GetAllRows()
        {
            List<Ilan> returnValue = new List<Ilan>();
            SqlCommand cmd = new SqlCommand("IlanGetir");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Tools.Connection();
            SqlDataReader dr;
            Tools.Open();

            dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                Ilan I = new Ilan();
                I.IlanID = dr["IlanID"] is DBNull ? 0 : Convert.ToInt32(dr["IlanID"]);
                I.KategoriID = dr["KategoriID"] is DBNull ? String.Empty : dr["KategoriID"].ToString();
                I.KullaniciAdi = dr["KullaniciAdi"] is DBNull ? String.Empty : dr["KullaniciAdi"].ToString();
                I.UyeID = dr["UyeID"] is DBNull ? 0 : Convert.ToInt32(dr["UyeID"]);
                I.UlkeID = dr["UlkeID"] is DBNull ? 0 : Convert.ToInt32(dr["UyeID"]);
                I.SehirID = dr["SehirID"] is DBNull ? 0 : Convert.ToInt32(dr["SehirID"]);
                I.IlceID = dr["IlceID"] is DBNull ? 0 : Convert.ToInt32(dr["IlceID"]);
                I.IlanTarihi = dr["IlanTarihi"] is DBNull ? DateTime.Now :Convert.ToDateTime(dr["IlanTarihi"]);
                I.MahalleID = dr["MahalleID"] is DBNull ? 0 : Convert.ToInt32(dr["MahalleID"]);
                I.IlanBasligi = dr["IlanBasligi"] is DBNull ? String.Empty : dr["IlanBasligi"].ToString();
                I.Aciklama = dr["Aciklama"] is DBNull ? String.Empty : dr["Aciklama"].ToString();
                I.Fiyat = dr["Fiyat"] is DBNull ? 0 : Convert.ToDecimal(dr["Fiyat"]);
                I.UlkeID = dr["ParaCinsiID"] is DBNull ? 0 : Convert.ToInt32(dr["ParaCinsiID"]);
                returnValue.Add(I);
            }
            
            dr.Dispose();
            return returnValue;
                      
        }

        public Ilan GetIlan(int RecordID)
        {
            SqlCommand cmd = new SqlCommand("IlanGetir");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@IlanID",SqlDbType.Int).Value= RecordID;
            cmd.Connection = Tools.Connection();
            
            SqlDataReader dr;
            Tools.Open();
            dr = cmd.ExecuteReader();

            dr.Read();
            Ilan I = new Ilan();
            I.IlanID = dr["IlanID"] is DBNull ? 0 : Convert.ToInt32(dr["IlanID"]);
            I.KategoriID = dr["KategoriID"] is DBNull ? String.Empty : dr["KategoriID"].ToString(); ;
            I.KullaniciAdi = dr["KullaniciAdi"] is DBNull ? String.Empty : dr["KullaniciAdi"].ToString();
            I.UyeID = dr["UyeID"] is DBNull ? 0 : Convert.ToInt32(dr["IlanID"]);
            I.UlkeID = dr["UlkeID"] is DBNull ? 0 : Convert.ToInt32(dr["IlanID"]);
            I.SehirID = dr["SehirID"] is DBNull ? 0 : Convert.ToInt32(dr["SehirID"]);
            I.IlceID = dr["IlceID"] is DBNull ? 0 : Convert.ToInt32(dr["IlceID"]);
            I.MahalleID = dr["MahalleID"] is DBNull ? 0 : Convert.ToInt32(dr["MahalleID"]);
            I.IlanBasligi = dr["IlanBasligi"] is DBNull ? String.Empty : dr["IlanBasligi"].ToString();
            I.Aciklama = dr["Aciklama"] is DBNull ? String.Empty : dr["Aciklama"].ToString();
            I.Fiyat = dr["Fiyat"] is DBNull ? 0 : Convert.ToDecimal(dr["Fiyat"]);
            I.UlkeID = dr["ParaCinsiID"] is DBNull ? 0 : Convert.ToInt32(dr["ParaCinsiID"]); ;

            dr.Dispose();
            return I;

        }
        public Ilan GetIlanByIlanID(int RecordID)
        {
            SqlCommand cmd = new SqlCommand("IlanGetirIlanID");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@IlanID", SqlDbType.Int).Value = RecordID;
            cmd.Connection = Tools.Connection();
            SqlDataReader dr;
            Tools.Open();
            dr = cmd.ExecuteReader();

            dr.Read();
            Ilan I = new Ilan();
            I.IlanID = dr["IlanID"] is DBNull ? 0 : Convert.ToInt32(dr["IlanID"]);
            I.KategoriID = dr["KategoriID"] is DBNull ? String.Empty : dr["KategoriID"].ToString(); ;
            I.KullaniciAdi = dr["KullaniciAdi"] is DBNull ? String.Empty : dr["KullaniciAdi"].ToString();
            I.UyeID = dr["UyeID"] is DBNull ? 0 : Convert.ToInt32(dr["IlanID"]);
            I.UlkeID = dr["UlkeID"] is DBNull ? 0 : Convert.ToInt32(dr["IlanID"]);
            I.SehirID = dr["SehirID"] is DBNull ? 0 : Convert.ToInt32(dr["SehirID"]);
            I.IlceID = dr["IlceID"] is DBNull ? 0 : Convert.ToInt32(dr["IlceID"]);
            I.MahalleID = dr["MahalleID"] is DBNull ? 0 : Convert.ToInt32(dr["MahalleID"]);
            I.IlanBasligi = dr["IlanBasligi"] is DBNull ? String.Empty : dr["IlanBasligi"].ToString();
            I.Aciklama = dr["Aciklama"] is DBNull ? String.Empty : dr["Aciklama"].ToString();
            I.Fiyat = dr["Fiyat"] is Decimal ? Convert.ToDecimal(dr["Fiyat"]) : 0;
            I.UlkeID = dr["ParaCinsiID"] is DBNull ? 0 : Convert.ToInt32(dr["ParaCinsiID"]);

            dr.Dispose();

            return I;

        }
        public List<Ilan> GetIlanByUyeID(int RecordID)
        {
            List<Ilan> returnValue = new List<Ilan>();
            SqlCommand cmd = new SqlCommand("GetIlanByUyeID");
            cmd.Parameters.AddWithValue("@UyeID",SqlDbType.Int).Value= RecordID;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Tools.Connection();
            SqlDataReader dr;
            Tools.Open();
            dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                Ilan I = new Ilan();
                I.IlanID = dr["IlanID"] is DBNull ? 0 : Convert.ToInt32(dr["IlanID"]);
                I.KategoriID = dr["KategoriID"] is DBNull ? String.Empty : dr["KategoriID"].ToString(); ;
                I.KullaniciAdi = dr["KullaniciAdi"] is DBNull ? String.Empty : dr["KullaniciAdi"].ToString();
                I.UyeID = dr["UyeID"] is DBNull ? 0 : Convert.ToInt32(dr["UyeID"]);
                I.UlkeID = dr["UlkeID"] is DBNull ? 0 : Convert.ToInt32(dr["UlkeID"]);
                I.SehirID = dr["SehirID"] is DBNull ? 0 : Convert.ToInt32(dr["SehirID"]);
                I.IlceID = dr["IlceID"] is DBNull ? 0 : Convert.ToInt32(dr["IlceID"]);
                I.MahalleID = dr["MahalleID"] is DBNull ? 0 : Convert.ToInt32(dr["MahalleID"]);
                I.IlanBasligi = dr["IlanBasligi"] is DBNull ? String.Empty : dr["IlanBasligi"].ToString();
                I.Aciklama = dr["Aciklama"] is DBNull ? String.Empty : dr["Aciklama"].ToString();
                I.Fiyat = dr["Fiyat"] is Decimal ? Convert.ToDecimal(dr["Fiyat"]) : 0;
                I.UlkeID = dr["ParaCinsiID"] is DBNull ? 0 : Convert.ToInt32(dr["ParaCinsiID"]);
                returnValue.Add(I);
            }
            dr.Dispose();

            return returnValue;

        }


        public List<Ilan> GetIlanAra(string Sql,SqlCommand cmd)
        {
            List<Ilan> returnValue = new List<Ilan>();
            cmd.CommandText = Sql;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = Tools.Connection();
            SqlDataReader dr;
            Tools.Open();

            dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                Ilan I = new Ilan();
                I.IlanID = dr["IlanID"] is DBNull ? 0 : Convert.ToInt32(dr["IlanID"]);
                I.KategoriID = dr["KategoriID"] is DBNull ? String.Empty : dr["KategoriID"].ToString();
                I.KullaniciAdi = dr["KullaniciAdi"] is DBNull ? String.Empty : dr["KullaniciAdi"].ToString();
                I.UyeID = dr["UyeID"] is DBNull ? 0 : Convert.ToInt32(dr["UyeID"]);
                I.UlkeID = dr["UlkeID"] is DBNull ? 0 : Convert.ToInt32(dr["UyeID"]);
                I.SehirID = dr["SehirID"] is DBNull ? 0 : Convert.ToInt32(dr["SehirID"]);
                I.IlceID = dr["IlceID"] is DBNull ? 0 : Convert.ToInt32(dr["IlceID"]);
                I.IlanTarihi = dr["RecordDate"] is DBNull ? DateTime.Now : Convert.ToDateTime(dr["RecordDate"]);
                I.MahalleID = dr["MahalleID"] is DBNull ? 0 : Convert.ToInt32(dr["MahalleID"]);
                I.IlanBasligi = dr["IlanBasligi"] is DBNull ? String.Empty : dr["IlanBasligi"].ToString();
                I.Aciklama = dr["Aciklama"] is DBNull ? String.Empty : dr["Aciklama"].ToString();
                I.Fiyat = dr["Fiyat"] is DBNull ? 0 : Convert.ToDecimal(dr["Fiyat"]);//Valla burda hata vere bilir Hocam ):
                I.UlkeID = dr["ParaCinsiID"] is DBNull ? 0 : Convert.ToInt32(dr["ParaCinsiID"]);
                returnValue.Add(I);
            }
            dr.Dispose();
            return returnValue;
        }

        
    }
}
